package util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

public class ExcelUtil {

    private ExcelUtil() {
    }

    /**
     * Excel数据转 Map列表
     *
     * @param file           excel 文件
     * @param sheetNum       工作表的编号, 从 0 开始
     * @param hasColumnNames true : 首行为列名， false : 首行为数据
     * @return
     * @throws IOException
     */
    public static List<Map<String, String>> getAttributeMapsFromExcel(File file, int sheetNum, boolean hasColumnNames)
            throws IOException {
        List<Map<String, String>> rowMapList = new ArrayList<>();
        processByGivenRowHandler(file, sheetNum, hasColumnNames, rowMapList::add);
        return rowMapList;
    }

    /**
     * 读取并按给定函数处理Excel文件的每行数据
     * @param file           excel 文件
     * @param sheetNum       工作表的编号, 从 0 开始
     * @param hasColumnNames true : 首行为列名， false : 首行为数据
     * @param rowHandler 消费型函数式接口，指定每行数据的处理逻辑
     * @throws IOException
     */
    public static void processByGivenRowHandler(File file, int sheetNum, boolean hasColumnNames, Consumer<Map<String, String>> rowHandler)
            throws IOException {
        InputStream stream = new FileInputStream(file);
        Workbook wb = null;
        try {
            String fileType = file.getName().split("\\.")[1];
            switch (fileType) {
                case "xls":
                    wb = new HSSFWorkbook(stream);
                    break;
                case "xlsx":
                    wb = new XSSFWorkbook(stream);
                    break;
                default:
                    throw new IOException("文件格式不正确，应为xls或xlsx");
            }

            Sheet sheet = wb.getSheetAt(sheetNum);

            if (hasColumnNames) {
                processSheetWithColumnNames(sheet, rowHandler);
            } else {
                processSheetWithoutColumnNames(sheet, rowHandler);
            }

        } finally {
            if (wb != null) {
                wb.close();
            }
            stream.close();
        }
    }

    private static void processSheetWithColumnNames(Sheet sheet, Consumer<Map<String, String>> rowHandler) {
        List<String> keys = new ArrayList<>();
        int length = sheet.getRow(0).getPhysicalNumberOfCells();

        for (Row row : sheet) {

            // 表头
            if (row.getRowNum() == 0) {
                for (int col = 0; col < length; col++) {
                    keys.add(getCellValue(row.getCell(col)));
                }

                continue;
            }

            // 获取数据，生成 key-value
            Map<String, String> rowMap = new HashMap<>();

            for (int col = 0; col < length; col++) {
                String key = keys.get(col);
                String value = getCellValue(row.getCell(col));

                rowMap.put(key, value);
            }

            rowHandler.accept(rowMap);

        }

    }

    private static void processSheetWithoutColumnNames(Sheet sheet, Consumer<Map<String, String>> rowHandler) {
        List<String> keys = new ArrayList<>();
        int length = sheet.getRow(0).getPhysicalNumberOfCells();

        // 构建表头：以字母表示列名
        for (int col = 0; col < length; col++) {
            keys.add(String.valueOf((char) ('A' + col)));
        }

        for (Row row : sheet) {

            // 获取数据，生成 key-value
            Map<String, String> rowMap = new HashMap<>();

            for (int col = 0; col < length; col++) {
                String key = keys.get(col);
                String value = getCellValue(row.getCell(col));

                rowMap.put(key, value);
            }

            rowHandler.accept(rowMap);

        }

    }

    private static String getCellValue(Cell cell) {
        String value;

        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();

                break;

            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue().toString();
                } else {
                    double number = cell.getNumericCellValue();

                    if (number - (int) number <= 0.000001) {
                        value = Integer.toString((int) number);
                    } else {
                        value = String.valueOf(cell.getNumericCellValue());
                    }
                }

                break;

            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());

                break;

            case FORMULA:
                value = String.valueOf(cell.getCellFormula());

                break;

            case BLANK:
                value = "";

                break;

            default:
                value = "";
        }

        return value;
    }
}